VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "Enrollment"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Const MAXCLASSES = 6
Function OverEnrolled(conn As ADODB.Connection, lngStudentID As Long) As Boolean
    Dim rsEnrollment As Object
    
    'Student cannot add if already at class limit
    strSQL = "select count(studentid) from enrollment where studentid = " & lngStudentID
    Set rsEnrollment = CreateObject("ADODB.recordset")
    rsEnrollment.ActiveConnection = conn
    rsEnrollment.Open strSQL, , adOpenKeyset
    If rsEnrollment.Fields(0).Value > MAXCLASSES Then
        OverEnrolled = True
    Else
        OverEnrolled = False
    End If
    rsEnrollment.Close
End Function
Function ClassCompleted(conn As ADODB.Connection, lngStudentID As Long, strClassID As String) As Boolean
    Dim rsEnrollment As Object
    
    'Student cannot drop a class if the class is completed
    'In other words, it has a grade
    strSQL = "select classid,studentid,grade from enrollment where studentid = " & lngStudentID & " AND classid ='" & strClassID & "'"
    Set rsEnrollment = conn.Execute(strSQL)
    
    'Check for no records even existing
    If rsEnrollment.BOF And rsEnrollment.EOF Then
       ClassCompleted = False
       Exit Function
    End If
    
    'Check for a non-null grade
    If Not IsNull(rsEnrollment.Fields("Grade")) Then
        ClassCompleted = True
    Else
        ClassCompleted = False
    End If
    rsEnrollment.Close
End Function
Public Function Add(ByVal lngStudentID As Long, ByVal strClassID As String) As Integer

    Dim conn As ADODB.Connection
    Dim strSQL As String
    
    'Set return value to 0
    Add = 0
    Set conn = CreateObject("ADODB.Connection")
    On Error GoTo ErrorHandler
    'Open connection with State University Database
    conn.Open "DSN=StateU;UID=sa;PWD=;"
     
    'Business rule
    'See if student is over enrolled
    If OverEnrolled(conn, lngStudentID) Then
        Add = 1
        Exit Function
    End If
    
    'Perform the work of adding the student to the class
    strSQL = "INSERT INTO enrollment (ClassID, StudentID) VALUES ('" & strClassID & "'," & lngStudentID & ")"
    conn.BeginTrans
    conn.Execute strSQL
    conn.CommitTrans
    
    'Close the connection
    conn.Close
    Exit Function

ErrorHandler:
    'Rollback the transaction if we got that far
    If Not IsEmpty(conn) Then conn.RollbackTrans
    
    'Raise an error to report back
    Add = 1
End Function

